from flask import jsonify, render_template, request

#
from blues.cloud import bp
from database import session_maker

# 实例维度- ECS综合查询
ecs_sql = """
SELECT
	x.*, y.disk_size_all,
	y.disk_usage_all
FROM
	(
		SELECT
			a.org,
			a.project,
			a.instance_id,
			c.cpu,
			c.memory,
		    MAX(b.cpu_max) as cpu_max_usage,
			MIN(b.cpu_min) as cpu_min_usage,
			ROUND(AVG(b.cpu_avg),2) as cpu_avg_usage,
			MAX(b.mem_max) as mem_max_usage,
			MIN(b.mem_min) as mem_min_usage,
			ROUND(AVG(b.mem_avg),2) as mem_avg_usage,
			ROUND(MAX(c.cpu * b.cpu_max) / 100, 2) AS cpu_max_used,
			ROUND(MIN(c.cpu * b.cpu_min) / 100, 2) AS cpu_min_used,
			ROUND(AVG(c.cpu * b.cpu_avg) / 100, 2) AS cpu_avg_used,
			ROUND(MAX(c.memory * b.mem_max) / 100, 2) AS mem_max_used,
			ROUND(MIN(c.memory * b.mem_min) / 100, 2) AS mem_min_used,
			ROUND(AVG(c.memory * b.mem_avg) / 100, 2) AS mem_avg_used
		FROM
			cmdb_cloud_resource a,
			cms_ecs b,
			c_ecs_info c
		WHERE
			a.instance_id = b.instance_id
		AND a.instance_id = c.instance_id
		AND a.is_deleted IS NULL
		AND a.res_type = 'ECS'
		AND b.date BETWEEN '{}'
		AND '{}'
		AND DATE_FORMAT(c.record_time, '%Y-%m-%d') = b.date
    GROUP BY a.org,a.project,a.instance_id,c.cpu,c.memory
	) x
LEFT JOIN (
	SELECT
		instance_id,
		SUM(disk_size) AS disk_size_all,
		ROUND(
			SUM(disk_size * disk_usage) / SUM(disk_size),
			2
		) AS disk_usage_all
	FROM
		(
			SELECT
				a.instance_id,
				b.disk_id,
				b.disk_size,
				b.disk_usage
			FROM
				cmdb_cloud_resource a,
				cms_ecs_disk b
			WHERE
				a.instance_id = b.instance_id
			AND a.is_deleted IS NULL
			AND b.date = (SELECT MAX(date) FROM cms_ecs_disk WHERE date<='{}')
		) disk_tmp
	GROUP BY
		instance_id
) y ON x.instance_id = y.instance_id;
"""

# 实例维度- RDS综合查询
rds_sql = """
SELECT
	a.org AS '组织',
	a.project AS '项目',
	a.instance_id AS '实例ID',
	c.cpu AS 'CPU核数',
	c.mem AS '内存GB',
	c.conn_max AS '最大连接数',
	MAX(d.cpu_max) AS 'CPU最大使用率%',
	MIN(d.cpu_min) AS 'CPU最小使用率%',
	ROUND(AVG(d.cpu_avg), 2) AS 'CPU平均使用率%',
	MAX(d.mem_max) AS '内存最大使用率%',
	MIN(d.mem_min) AS '内存最小使用率%',
	ROUND(AVG(d.mem_avg), 2) AS '内存平均使用率%',
	MAX(d.con_max) AS '连接数最大使用率%',
	MIN(d.con_min) AS '连接数最小使用率%',
	ROUND(AVG(d.con_avg), 2) AS '连接数平均使用率%',
	MAX(d.disk_max) AS '磁盘最大使用率%'
FROM
	cmdb_cloud_resource a,
	c_rds_info b,
	(
		SELECT
			db_instance_class,
			cpu,
			mem,
			conn_max
		FROM
			class_rds
		GROUP BY
			db_instance_class,
			cpu,
			mem,
			conn_max
	) c,
	cms_rds d
WHERE
	a.res_type = 'RDS'
AND a.is_deleted IS NULL
AND a.instance_id = b.db_instance_id
AND a.instance_id = d.instance_id
AND d.date BETWEEN '{}'
AND '{}'
AND b.db_instance_class = c.db_instance_class
GROUP BY
	a.org,
	a.project,
	a.instance_id,
	c.cpu,
	c.mem,
	c.conn_max;
"""

# 实例维度- OSS综合查询， 按照截止时间来统计
oss_sql = """
SELECT
	a.org AS '组织',
	a.project AS '项目',
	a.instance_id AS '实例ID',
	b.capacity AS '存储配额(GB)',
	ROUND( 100 * b.size_in_bytes / ( IF ( b.capacity =- 1, 100, b.capacity ) * 1000000000 ), 2 ) AS '使用率%' 
FROM
	cmdb_cloud_resource a,
	c_oss_info b 
WHERE
	a.instance_id = b.bucket 
	AND a.is_deleted IS NULL 
	AND DATE_FORMAT( b.record_time, '%Y-%m-%d' ) = ( SELECT DATE_FORMAT( MAX( record_time ), '%Y-%m-%d' ) FROM c_oss_info WHERE DATE_FORMAT( record_time ,'%Y-%m-%d' ) <= '{}' )
"""

eip_sql = """
SELECT
	a.org AS '组织',
	a.project AS '项目',
	a.instance_id AS '实例ID',
	a.ip_public AS '公网IP',
	ROUND( 100 * MAX( b.net_rx_max ) / ( b.bandwidth * 1000000 ), 2 ) AS '带宽最大使用率%',
	ROUND( 100 * MIN( b.net_rx_min ) / ( b.bandwidth * 1000000 ), 2 ) AS '带宽最小使用率%',
	ROUND( 100 * AVG( b.net_rx_avg ) / AVG( b.bandwidth * 1000000 ), 2 ) AS '带宽平均使用率%',
	b.bandwidth AS '带宽Mbps'
FROM
	cmdb_cloud_resource a,
	cms_eip b
WHERE
	a.res_type = 'EIP'
	AND a.is_deleted IS NULL
	AND a.instance_id = b.instance_id
	AND b.date BETWEEN '{}'
	AND '{}'
GROUP BY
	a.org,
	a.project,
	a.instance_id,
	a.ip_public;
"""

#  实例维度- ODPS综合查询， 注意查询时间格式 不带横线，如：20230901
odps_sql = """
SELECT
	y.org AS '组织',
	y.project AS '项目',
	x.* 
FROM
	(
	SELECT
		a.project,
		ROUND( IFNULL( SUM( a.total_cost_cpu ), 0 ) / 100 / 3600, 0 ) AS 'CPU消耗(核*小时)',
		ROUND( IFNULL( SUM( a.total_cost_mem ), 0 ) / 1024 / 3600, 0 ) AS '内存消耗(GB*小时)',
		IFNULL( SUM( a.total_task_num ), 0 ) AS '任务数',
		ROUND( b.data_filelogicallength / 1024 / 1024 / 1024, 2 ) AS '存储使用量(GB)',
		ROUND( b.project_quota_size / 1024 / 1024 / 1024 / 3, 0 ) AS '存储配额(GB)',
		ROUND( ( b.data_filelogicallength * 3 / b.project_quota_size ) * 100, 2 ) AS '存储使用率%' 
	FROM
		abm_saas_ag.odps_project_info_topn a,
		abm_saas_ag.region_odps_project_storage_info_realtime b 
	WHERE
		a.project = b.project 
		AND a.ds BETWEEN '{}' 
		AND '{}' 
	GROUP BY
		a.project 
	) x
	RIGHT JOIN ( SELECT org, project, instance_id FROM cmdb_cloud_resource WHERE res_type = 'ODPS' AND is_deleted IS NULL ) y ON x.project = y.instance_id;
"""


def ecs_instance_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(ecs_sql.format(start, end, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'instance_id': row[2], 'cpu': row[3], 'memory': row[4],
                'cpu_max_usage': row[5], 'cpu_min_usage': row[6], 'cpu_avg_usage': row[7],
                'mem_max_usage': row[8], 'mem_min_usage': row[9], 'mem_avg_usage': row[10],
                'cpu_max_used': row[11], 'cpu_min_used': row[12], 'cpu_avg_used': row[13],
                'mem_max_used': row[14], 'mem_min_used': row[15], 'mem_avg_used': row[16],
                'disk_size_all': row[17], 'disk_usage_all': row[18]
            }
            data_li.append(tmp_dict)
    return data_li


def rds_instance_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(rds_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'instance_id': row[2], 'cpu': row[3], 'mem': row[4],
                'conn_max': row[5], 'cpu_max': row[6], 'cpu_min': row[7], 'cpu_avg': row[8], 'mem_max': row[9],
                'mem_min': row[10], 'mem_avg': row[11], 'con_max': row[12], 'con_min': row[13], 'con_avg': row[14],
                'disk_max': row[15]
            }
            data_li.append(tmp_dict)
    return data_li


def oss_instance_stats(end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(oss_sql.format(end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'bucket': row[2], 'capacity': row[3], 'used_size': row[4]
            }
            data_li.append(tmp_dict)
    return data_li


def eip_instance_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(eip_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'instance_id': row[2], 'public_ip': row[3], 'net_rx_max': row[4],
                'net_rx_min': row[5], 'net_rx_avg': row[6], 'bandwidth': row[7]
            }
            data_li.append(tmp_dict)
    return data_li


def odps_instance_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(odps_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'odps_project': row[2], 'cpu_core_hours': row[3],
                'mem_gb_hours': row[4], 'tasks': row[5], 'storage_used_size_gb': row[6], 'storage_quota_gb': row[7],
                'storage_usage': row[8]
            }
            data_li.append(tmp_dict)
    return data_li


@bp.route('/api/v1/cloud/ecs_instance_stats', methods=['GET'])
def ecs_instance_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print("ecs instance stats:", start, end)
    data_li = ecs_instance_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/rds_instance_stats', methods=['GET'])
def rds_instance_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = rds_instance_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/oss_instance_stats', methods=['GET'])
def oss_instance_stats_api():
    end = request.args.get('end')  # 2023-09-20
    data_li = oss_instance_stats(end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/eip_instance_stats', methods=['GET'])
def eip_instance_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = eip_instance_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/odps_instance_stats', methods=['GET'])
def odps_instance_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    # odps的日期传参需要删除多余的符号"-"
    start = start.replace("-", "")
    end = end.replace("-", "")
    print(start, end)
    data_li = odps_instance_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


# 渲染web页面
@bp.route('/cloud/instance_stats')
def resource_instance_stats_page():
    return render_template('resource_instance_stats.html', active='cloud_instance')
